package network.jdbc;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

public class SQLExecutor {

	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/STOREDB";
		String user = "root";
		String password = "123456";
		
		String sqlFile = "/home/glogo/schema.sql";
		BufferedReader br = null;
		
		Connection conn = null;
		Statement stmt  = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(url, user, password);
			stmt = conn.createStatement();
			
			br = new BufferedReader(new FileReader(new File(sqlFile)));
			
			String data = null;
			String sql = "";
			
			while((data=br.readLine()) != null){
				data = data.trim();
				if(data.length() == 0)	continue;
				sql = sql + data;
				if(sql.substring(sql.length()-1).equals(";")){
					System.out.println(sql);
					boolean hasResult = stmt.execute(sql);
					if(hasResult)
						showResultSet(stmt.getResultSet());
					sql = "";
				}
			}
			
		} catch (FileNotFoundException e) {
			System.out.println("很抱歉，文件没有找到！");
			System.exit(0);
		} catch (IOException e) {
			e.printStackTrace();
			System.exit(0);
		} catch (ClassNotFoundException e) {
			System.out.println("加载MySQL数据库驱动失败！");
			e.printStackTrace();
		} catch (SQLException e) {
			System.out.println("取得数据库连接失败！");
			e.printStackTrace();
		}finally{
			if(br != null)
				try {
					br.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			if(conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			if(stmt != null)
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
		
	}
	
	public static void showResultSet(ResultSet rs){
		try {
			ResultSetMetaData rsMeta = rs.getMetaData();
			int columnCount = rsMeta.getColumnCount();
			for(int i = 1; i<=columnCount; i++){
				System.out.print(rsMeta.getColumnLabel(i));
				int type = rsMeta.getColumnType(i);
//				System.out.print("(" + rsMeta.getColumnType(i) + ")\t");
				switch(type){
				case Types.INTEGER:
					System.out.print("(INT)\t");	break;
				case Types.BIGINT:
					System.out.print("(BIGINT)\t");	break;
				case Types.VARCHAR:
					System.out.print("(VARCHAR)\t");	break;
				case Types.FLOAT:
					System.out.print("(FLOAT)\t");	break;
				}
			}
			System.out.println();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
}
